﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Diagnostics;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.POIFS.FileSystem;
using NPOI.HPSF;

public partial class UC_BCDTMacHD : System.Web.UI.UserControl
{
    SqlParameter[] p;
    clsXuLy ex = new clsXuLy();
    NpoiExport cl = new NpoiExport();
    string frmName = "frmBaoCaoDoanhThu";
    static int f_SuDung = 0, f_XemTatCa = 0, f_InBaoCao = 0;
    public string Header = System.Configuration.ConfigurationSettings.AppSettings["Header"].ToString();
    static int PageNumber = 10;
    static int CurrentPage = 1;
    static double kltx = 0, klban = 0, ttvl = 0, ttvlkhongvat = 0, ttvlth = 0, ttban = 0, ttbankhongvat = 0, tongchenh = 0, chenhnvkd = 0, chenhcongty = 0, ttchenh = 0, ttnvkd = 0, ttcongty = 0, ttcongtron = 0, ttcvc = 0, dthugiavl = 0, dthuvlth = 0, hotrocuoc = 0, tiencabom = 0, tienhang = 0;
    static string ncc = "";
    static System.Data.DataTable dt;
    static System.Data.DataTable dtPrint;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(Convert.ToString(Session["user"])) || string.IsNullOrEmpty(Convert.ToString(Session["IDUser"])))
            Response.Redirect("Default.aspx");
        if (!IsPostBack)
        {
            string idnguoidung = Session["IDUser"].ToString();
            string sql = "select SuDung,Them,Sua,Xoa,InBaoCao,XemTatCa from XDAPhanQuyen where IDnguoidung='" + idnguoidung + "' and tag='" + frmName + "'";

            System.Data.DataTable dt2 = ex.GetData_Text(sql);
            if (dt2.Rows.Count > 0)
            {
                f_SuDung = int.Parse(dt2.Rows[0]["SuDung"].ToString());
                f_InBaoCao = int.Parse(dt2.Rows[0]["InBaoCao"].ToString());
                f_XemTatCa = int.Parse(dt2.Rows[0]["XemTatCa"].ToString());
            }


            CurrentPage = 1;
            
            for (int i = 1; i <= 12; i++)
            {
                dlThang.Items.Add(new ListItem("Tháng " + i.ToString(), i.ToString()));
            }
            dlThang.Items.Insert(0, new ListItem("Theo thời gian", ""));
            dlThang.SelectedIndex = 0;

            int year1 = DateTime.Now.Year - 3;
            int year2 = DateTime.Now.Year;
            for (int i = year2; i >= year1; i--)
            {
                dlNam.Items.Add(new ListItem("Năm " + i.ToString(), i.ToString()));
            }
            dlNam.SelectedValue = year2.ToString();

            txtTuNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
            txtDenNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
            dlNam.Visible = false;


            LoadKhachHang(dlKhachHang);
            LoadLoaiKH(dlLoaiKH);
            LoadTramTron(dlTramTron);
            LoadCongTrinh(dlCongTrinh);
            LoadMacHopDong(dlMacHD);
            LoadLoaiDa(dlLoaiDa);
        }
    }
    void LoadTramTron(DropDownList dlTramTron)
    {
        dlTramTron.Items.Clear();
        dlTramTron.DataSource = ex.GetData("sp_xdanhaphang_ListTramTron");
        dlTramTron.DataBind();
        dlTramTron.Items.Insert(0, new ListItem("--Chọn trạm trộn--", ""));
        dlTramTron.SelectedIndex = 0;
    }
    void LoadLoaiKH(DropDownList dlLoaiKH)
    {
        dlLoaiKH.Items.Clear();
        dlLoaiKH.DataSource = ex.GetData("sp_xdaloaikhachhang_ListDrop");
        dlLoaiKH.DataBind();
        dlLoaiKH.Items.Insert(0, new ListItem("--Chọn LKH--", ""));
        dlLoaiKH.SelectedIndex = 0;
    }
    void LoadKhachHang(DropDownList dl)
    {
        dl.Items.Clear();
        dl.Items.Insert(0, new ListItem("--Chọn khách hàng--", ""));
        dl.SelectedIndex = 0;
    }
    void LoadCongTrinh(DropDownList dl)
    {
        dl.Items.Clear();
        dl.Items.Insert(0, new ListItem("--Chọn công trình--", ""));
        dl.SelectedIndex = 0;
    }
    protected void dlLoaiBaoCao_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlLoaiBaoCao.SelectedIndex.Equals(0))
            Response.Redirect("BCDTTongHop.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(1))
            Response.Redirect("BCDTMacHopDong.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(2))
            Response.Redirect("BCDTBienSoXe.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(3))
            Response.Redirect("BCDTNVKD.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(4))
            Response.Redirect("BCDTXeBom.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(5))
            Response.Redirect("BCDTGiaVatLieu.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(6))
            Response.Redirect("BCDTKhachHang.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(7))
            Response.Redirect("BCDTVatLieuTieuHao.aspx");
    }
    protected void btnXem_Click(object sender, EventArgs e)
    {
        if (f_SuDung.Equals(1))
        {
            CurrentPage = 1;
            btnNext.Enabled = true;
            Search(1);
        }
        else gstGetMess("Bạn không có quyền xem báo cáo này", "");
    }
    void PrintNPOI(System.Data.DataTable dt)
    {
        var workbook = new HSSFWorkbook();

        if (dt.Rows.Count > 0)
        {
            string sheetname = "Bảng tổng hợp";

            if (dlKhachHang.SelectedIndex > 0)
                sheetname = dlKhachHang.SelectedItem.Text;
            else if (dlLoaiKH.SelectedIndex > 0)
                sheetname = "Bảng tổng hợp-" + dlLoaiKH.SelectedItem.Text;
            else sheetname = "Bảng tổng hợp";

            if (dlCongTrinh.SelectedIndex > 0)
                sheetname = dlCongTrinh.SelectedItem.Text;

            var sheet = workbook.CreateSheet(sheetname);

            #region CSS
            var headerLabelCellStyle = workbook.CreateCellStyle();
            headerLabelCellStyle.Alignment = HorizontalAlignment.LEFT;
            headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
            headerLabelCellStyle.BorderLeft = CellBorderType.THIN;
            headerLabelCellStyle.BorderRight = CellBorderType.THIN;
            headerLabelCellStyle.BorderTop = CellBorderType.THIN;

            var headerLabelFont = workbook.CreateFont();
            headerLabelFont.FontName = "Times New Roman";

            headerLabelFont.FontHeightInPoints = 11;
            headerLabelFont.Color = HSSFColor.BLACK.index;
            headerLabelCellStyle.SetFont(headerLabelFont);

            //--------------------------

            var hsRight = workbook.CreateCellStyle();
            hsRight.Alignment = HorizontalAlignment.RIGHT;
            hsRight.BorderBottom = CellBorderType.THIN;
            hsRight.BorderLeft = CellBorderType.THIN;
            hsRight.BorderRight = CellBorderType.THIN;
            hsRight.BorderTop = CellBorderType.THIN;

            hsRight.SetFont(headerLabelFont);
            //-------------------
            var hsCenter = workbook.CreateCellStyle();
            hsCenter.Alignment = HorizontalAlignment.CENTER;
            hsCenter.BorderBottom = CellBorderType.THIN;
            hsCenter.BorderLeft = CellBorderType.THIN;
            hsCenter.BorderRight = CellBorderType.THIN;
            hsCenter.BorderTop = CellBorderType.THIN;

            hsCenter.SetFont(headerLabelFont);
            //-----------------------

            var hs1 = workbook.CreateCellStyle();
            hs1.Alignment = HorizontalAlignment.LEFT;

            var hsb = workbook.CreateFont();
            hsb.Boldweight = (short)FontBoldWeight.BOLD;
            hsb.FontName = "Times New Roman";

            hsb.FontHeightInPoints = 11;
            hsb.Color = HSSFColor.BLACK.index;
            hs1.SetFont(hsb);

            //-------------
            var hs2 = workbook.CreateCellStyle();
            hs2.Alignment = HorizontalAlignment.CENTER;

            var hsb2 = workbook.CreateFont();
            hsb2.Boldweight = (short)FontBoldWeight.BOLD;
            hsb2.FontName = "Times New Roman";

            hsb2.FontHeightInPoints = 18;
            hsb2.Color = HSSFColor.BLACK.index;
            hs2.SetFont(hsb2);
            //------------------------------
            var hs3 = workbook.CreateCellStyle();
            hs3.Alignment = HorizontalAlignment.CENTER;
            //hs3.BorderBottom = CellBorderType.THIN;
            var hsb3 = workbook.CreateFont();
            hsb3.Boldweight = (short)FontBoldWeight.BOLD;
            hsb3.FontName = "Times New Roman";

            hsb3.FontHeightInPoints = 14;
            hsb3.Color = HSSFColor.BLACK.index;
            hs3.SetFont(hsb3);

            //----------------------------
            var hs4 = workbook.CreateCellStyle();
            hs4.Alignment = HorizontalAlignment.CENTER;

            hs4.BorderBottom = CellBorderType.THIN;
            hs4.BorderLeft = CellBorderType.THIN;
            hs4.BorderRight = CellBorderType.THIN;
            hs4.BorderTop = CellBorderType.THIN;

            var hsb4 = workbook.CreateFont();
            hsb4.Boldweight = (short)FontBoldWeight.BOLD;
            hsb4.FontName = "Times New Roman";

            hsb4.FontHeightInPoints = 11;
            hsb4.Color = HSSFColor.BLACK.index;
            hs4.SetFont(hsb4);

            #endregion

            //Start header-----------------------------------------------------------------------
            #region Header
            //tao hnag dau tien
            var rowIndex = 0;

            //cong ty
            var row = sheet.CreateRow(rowIndex);
            Cell r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 4);
            sheet.AddMergedRegion(cra);

            //chi nhanh
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(1, 1, 0, 4);
            sheet.AddMergedRegion(cra);

            //tieu de bao cao
            string ngaythang = dlThang.SelectedIndex > 0 ? dlThang.SelectedItem.Text.ToUpper() + " " + dlNam.SelectedItem.Text.ToUpper() : "TỪ " + txtTuNgay.Text + " ĐẾN " + txtDenNgay.Text;
            string mac = dlMacHD.SelectedIndex > 0 ? " - Mác " + dlMacHD.SelectedItem.Text : "";
            string loaida = dlLoaiDa.SelectedIndex > 0 ? " - " + dlLoaiDa.SelectedItem.Text : "";
            string congtrinh = dlCongTrinh.SelectedIndex > 0 ? " - " + dlCongTrinh.SelectedItem.Text : "";
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("BÁO CÁO TỔNG HỢP DOANH THU " + ngaythang);
            r1c1.CellStyle = hs2;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(2, 2, 0, 12);
            sheet.AddMergedRegion(cra);

            //khachhang
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue(dlKhachHang.SelectedIndex > 0 ? "Khách hàng: " + dlKhachHang.SelectedItem.Text + congtrinh + mac + loaida : dlLoaiKH.SelectedIndex > 0 ? "Loại khách hàng: " + dlLoaiKH.SelectedItem.Text : "");
            r1c1.CellStyle = hs3;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(3, 3, 0, 12);
            sheet.AddMergedRegion(cra);

            //freeze panes
            sheet.CreateFreezePane(0, 6);
            #endregion
            //End header-----------------------------------------------------------------------

            #region Header1-2

            //header2
            rowIndex++;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            string[] header = { "Ngày xuất", "Số phiếu", "Biển số", "Lái xe", "Giờ xuất", "Khách hàng", "Độ sụt", "Mác TX", "KL TX", "Mác HĐ", "KL bán", "Loại đá", "Phụ gia", "Giá VL có VAT", "Giá VL không VAT", "TTVL có VAT", "TTVL không VAT", "Thành tiền VLTH", "Giá HĐ có VAT", "Giá HĐ không VAT", "TT bê tông có VAT", "TT bê tông không VAT", "Tổng chênh", "Chênh NVKD", "Chênh công ty", "TT chênh", "TT NVKD", "TT công ty", "Công trộn", "TT công trộn", "Cước VC", "Thành tiền CVC", "Doanh thu giá VL", "Doanh thu VLTH", "Tiền hỗ trợ cước", "Loại ca bơm", "Tiền ca bơm", "Thành tiền hàng", "NVKD", "Hình thức HĐ", "VAT" };

            var cell = row.CreateCell(0);
            cell.SetCellValue("STT");
            cell.CellStyle = hs4;
            cell.Row.Height = 400;


            //tao tieu de cot
            for (int j = 0; j < header.Length; j++)
            {
                cell = row.CreateCell(j + 1);
                cell.SetCellValue(header[j].ToString());
                cell.CellStyle = hs4;
                cell.Row.Height = 500;
            }
            #endregion

            int sodong = 0;
            //ghi du lieu tung dong
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                cell = row.CreateCell(0);
                cell.SetCellValue((i + 1).ToString());
                cell.CellStyle = hsCenter;

                for (int j = 1; j <= dt.Columns.Count - 5; j++)
                {
                    cell = row.CreateCell(j);

                    if (j == 9 || j == 11 || (j >= 14 && j <= 35) || (j >= 37 && j <= 38))
                    {
                        if (!dt.Rows[i][j - 1].ToString().Trim().Equals(""))
                        {
                            cell.SetCellType(CellType.NUMERIC);
                            cell.SetCellValue(double.Parse(dt.Rows[i][j - 1].ToString()));
                        }
                        else
                            cell.SetCellValue(dt.Rows[i][j - 1].ToString());
                        cell.CellStyle = hsRight;
                    }
                    else
                    {
                        cell.SetCellValue(dt.Rows[i][j - 1].ToString());

                        if (j <= 3 || j == 5 || (j >= 7 && j <= 12) || j == 41)
                            cell.CellStyle = hsCenter;
                        else
                            cell.CellStyle = headerLabelCellStyle;
                    }
                    cell.Row.Height = 500;
                }
                if (i > 0)
                {
                    if (dt.Rows[i][0].ToString().Equals(dt.Rows[i - 1][0].ToString()) && dt.Rows[i][5].ToString().Equals(dt.Rows[i - 1][5].ToString()) && dt.Rows[i][35].ToString().Equals(dt.Rows[i - 1][35].ToString()) && dt.Rows[i][35].ToString().ToLower().Contains("ca"))
                    {
                        sodong++;
                        cra = new CellRangeAddress(rowIndex - sodong, rowIndex, 37, 37);
                        sheet.AddMergedRegion(cra);
                    }
                    else
                        sodong = 0;
                }
            }

            #region Footer

            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            cell = row.CreateCell(6);
            cell.SetCellValue("Tổng cộng");
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            string[] footer = { "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM" };
            for (int k = 9; k <= 38; k++)
            {

                if (k == 37)
                {
                    if (GV.Rows.Count > 0)
                    {
                        cell = row.CreateCell(k);
                        cell.SetCellValue(GV.FooterRow.Cells[38].Text.Trim().Equals("&nbsp;") ? "0" : GV.FooterRow.Cells[38].Text);
                        cell.Row.Height = 500;
                        cell.CellStyle = hs4;
                    }
                }
                if (k != 10 && k != 12 && k != 13 && k != 36 && k != 37)
                {
                    cell = row.CreateCell(k);
                    if (k == 14 || k == 15 || k == 19 || k == 20 || k == 29 || k == 31)
                        cell.CellFormula = "AVERAGE(" + footer[k - 9] + "7:" + footer[k - 9] + rowIndex.ToString() + ")";
                    else
                    {
                        if (k == 38)
                            cell.CellFormula = "Sum(" + footer[k - 9] + "7:" + footer[k - 9] + rowIndex.ToString() + ")+AL" + (rowIndex + 1).ToString();
                        else
                            cell.CellFormula = "Sum(" + footer[k - 9] + "7:" + footer[k - 9] + rowIndex.ToString() + ")";
                    }
                    cell.Row.Height = 500;
                    cell.CellStyle = hs4;
                }

            }
            //fix lai border
            for (int k = 0; k <= 41; k++)
            {
                if ((k != 6 && k != 37 && k <= 8) || k == 10 || k == 12 || k == 13 || k == 36 || k == 39 || k == 40 || k == 41)
                {
                    r1c1 = row.CreateCell(k);
                    r1c1.CellStyle = hs4;
                    r1c1.Row.Height = 400;
                }
            }
            for (int ik = 0; ik <= 41; ik++)
            {
                sheet.AutoSizeColumn(ik);
            }
            #endregion

            //end sheet tong hop--------------------------

            //sp_BCDT_MacHD_Print2


            //in tung mac hd va loai da vao tung sheet
            if (dlKhachHang.SelectedIndex > 0)
            {
                #region LoadCongtrinh
                string[] header2 = { "Ngày xuất", "Số phiếu", "Biển số xe", "Khách hàng", "Mác HĐ", "KL bán", "Loại đá", "Giá VL", "Giá HĐ", "Thành tiền bán", "Hỗ trợ cước", "Loại ca bơm", "Tiền ca bơm", "NVKD", "Hình thức HĐ", "VAT" };

                int index = 1;
                if (dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
                    index = 1;
                else if (dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex > 0 && dlLoaiDa.SelectedIndex.Equals(0))
                    index = 2;
                else if (dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex > 0 && dlLoaiDa.SelectedIndex > 0)
                    index = 3;
                else if (dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex.Equals(0) && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
                    index = 4;

                getDate();
                p = new SqlParameter[7];
                p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                p[0].SqlDbType = SqlDbType.SmallDateTime;
                p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                p[1].SqlDbType = SqlDbType.SmallDateTime;
                p[2] = new SqlParameter("@Index", index);
                p[2].SqlDbType = SqlDbType.Int;
                p[3] = new SqlParameter("@CongTrinh", ex.GetGuid(dlCongTrinh.SelectedValue));
                p[3].SqlDbType = SqlDbType.UniqueIdentifier;
                p[4] = new SqlParameter("@MacHD", ex.GetGuid(dlMacHD.SelectedValue));
                p[4].SqlDbType = SqlDbType.UniqueIdentifier;
                p[5] = new SqlParameter("@LoaiDa", int.Parse(dlLoaiDa.SelectedValue));
                p[5].SqlDbType = SqlDbType.Int;
                p[6] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
                p[6].SqlDbType = SqlDbType.UniqueIdentifier;
                DataTable dt1 = ex.GetData("sp_BCDT_MacHD_CongTrinh_Print", p);

                int STT = 0;
                //ghi sheet con------------------------------------------
                sodong = 0;
                for (int i = 0; i < dt1.Rows.Count; i++)
                {
                    STT++;
                    #region Start
                    if (i > 0)
                    {
                        ncc = dt1.Rows[i]["f_MacHD"].ToString() + "-" + dt1.Rows[i]["LoaiDa"].ToString();

                        if (!ncc.Equals(dt1.Rows[i - 1]["f_MacHD"].ToString() + "-" + dt1.Rows[i - 1]["LoaiDa"].ToString()))
                        {

                            STT = 0;
                            #region Footer

                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(4);
                            cell.SetCellValue("Tổng cộng");
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;

                            string[] footer2 = { "G", "H", "I", "J", "K", "L", "M", "N", "O", "P" };
                            for (int k = 6; k <= 15; k++)
                            {

                                if (k == 6 || (k > 9 && k <= 11))
                                {
                                    cell = row.CreateCell(k);
                                    cell.CellFormula = "Sum(" + footer2[k - 6] + "7:" + footer2[k - 6] + rowIndex.ToString() + ")";
                                    cell.Row.Height = 500;
                                    cell.CellStyle = hs4;
                                }

                            }
                            //fix lai border
                            for (int k = 0; k <= dt1.Columns.Count - 5; k++)
                            {
                                if (k != 4 && k != 6 && k != 10 && k != 11)
                                {
                                    r1c1 = row.CreateCell(k);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }
                            }
                            for (int ik = 0; ik <= dt1.Columns.Count - 5; ik++)
                            {
                                sheet.AutoSizeColumn(ik);
                            }
                            #endregion

                            sheet = workbook.CreateSheet(cl.EscapeSheetName(ncc));
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header
                            //tao hnag dau tien

                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("BÁO CÁO TỔNG HỢP DOANH THU " + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 15);
                            sheet.AddMergedRegion(cra);

                            string ct = dlCongTrinh.SelectedIndex > 0 ? "-" + dlCongTrinh.SelectedItem.Text.Trim() : "";
                            //khachhang
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Khách hàng: " + dlKhachHang.SelectedItem.Text + ct + "-" + ncc);
                            r1c1.CellStyle = hs3;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(3, 3, 0, 15);
                            sheet.AddMergedRegion(cra);

                            //freeze panes
                            sheet.CreateFreezePane(0, 6);
                            #endregion

                            #region Header1-2

                            //header2

                            rowIndex = rowIndex + 2;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(0);
                            cell.SetCellValue("STT");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;


                            //tao tieu de cot
                            for (int j = 0; j < header2.Length; j++)
                            {
                                cell = row.CreateCell(j + 1);
                                cell.SetCellValue(header2[j].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                            }
                            #endregion
                            //End header-----------------------------------------------------------------------

                            STT++;

                        }
                    }
                    else
                    {
                        ncc = "";
                        if (!ncc.Equals(dt1.Rows[i]["f_MacHD"].ToString() + "-" + dt1.Rows[i]["LoaiDa"].ToString()))
                        {
                            ncc = dt1.Rows[i]["f_MacHD"].ToString() + "-" + dt1.Rows[i]["LoaiDa"].ToString();
                            sheet = workbook.CreateSheet(cl.EscapeSheetName(ncc));
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header
                            //tao hnag dau tien

                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("BÁO CÁO TỔNG HỢP DOANH THU " + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 15);
                            sheet.AddMergedRegion(cra);
                            string ct = dlCongTrinh.SelectedIndex > 0 ? "-" + dlCongTrinh.SelectedItem.Text.Trim() : "";
                            //khachhang
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Khách hàng: " + dlKhachHang.SelectedItem.Text + ct + "-" + ncc);
                            r1c1.CellStyle = hs3;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(3, 3, 0, 15);
                            sheet.AddMergedRegion(cra);

                            //freeze panes
                            sheet.CreateFreezePane(0, 6);
                            #endregion
                            //End header-----------------------------------------------------------------------
                            #region Header1-2

                            //header2

                            rowIndex = rowIndex + 2;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(0);
                            cell.SetCellValue("STT");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;

                            //tao tieu de cot
                            for (int j = 0; j < header2.Length; j++)
                            {
                                cell = row.CreateCell(j + 1);
                                cell.SetCellValue(header2[j].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                            }
                            #endregion
                        }
                        for (int ik = 0; ik <= dt1.Columns.Count; ik++)
                        {
                            sheet.AutoSizeColumn(ik);
                        }
                    }
                    #endregion
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);

                    cell = row.CreateCell(0);
                    cell.SetCellValue((STT).ToString());
                    cell.CellStyle = hsCenter;


                    for (int j = 1; j < dt1.Columns.Count - 5; j++)
                    {

                        cell = row.CreateCell(j);
                        if (j == 6 || j == 13 || (j >= 8 && j <= 11))
                        {
                            if (!dt1.Rows[i][j - 1].ToString().Trim().Equals(""))
                            {
                                cell.SetCellType(CellType.NUMERIC);
                                cell.SetCellValue(double.Parse(dt1.Rows[i][j - 1].ToString()));
                            }
                            else
                                cell.SetCellValue(dt1.Rows[i][j - 1].ToString());
                        }
                        else
                            cell.SetCellValue(dt1.Rows[i][j - 1].ToString());

                        if (j <= 3 || (j >= 5 && j <= 7) || j == 12 || j >= 14 || j == 41)
                            cell.CellStyle = hsCenter;
                        else
                            if (j == 4)
                                cell.CellStyle = headerLabelCellStyle;
                            else
                                cell.CellStyle = hsRight;
                        cell.Row.Height = 500;
                    }
                    if (i > 0)
                    {
                        if (dt1.Rows[i][0].ToString().Equals(dt1.Rows[i - 1][0].ToString()) && dt1.Rows[i][3].ToString().Equals(dt1.Rows[i - 1][3].ToString()) && dt1.Rows[i][11].ToString().Equals(dt1.Rows[i - 1][11].ToString()) && dt1.Rows[i][11].ToString().ToLower().Contains("ca"))
                        {
                            sodong++;
                            cra = new CellRangeAddress(rowIndex - sodong, rowIndex, 13, 13);
                            sheet.AddMergedRegion(cra);
                        }
                        else
                            sodong = 0;
                    }

                    if (i == dt1.Rows.Count - 1)
                    {
                        #region Footer

                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);

                        cell = row.CreateCell(4);
                        cell.SetCellValue("Tổng cộng");
                        cell.Row.Height = 500;
                        cell.CellStyle = hs4;

                        //cabom = ReturnCaBomCongTrinh(dt1.Rows[i - 1]["f_TuNgay"].ToString(), dt1.Rows[i - 1]["f_DenNgay"].ToString(), dt1.Rows[i - 1]["f_CongTrinh"].ToString());

                        string[] footer2 = { "G", "H", "I", "J", "K", "L", "M", "N", "O", "P" };
                        for (int k = 6; k <= 15; k++)
                        {
                            if (k == 13)
                            {
                                cell = row.CreateCell(k);
                                cell.SetCellType(CellType.NUMERIC);
                                cell.SetCellValue(GV.FooterRow.Cells[38].Text.Trim().Equals("&nbsp;") ? "0" : GV.FooterRow.Cells[38].Text);
                                cell.Row.Height = 500;
                                cell.CellStyle = hs4;
                            }
                            if (k == 6 || (k > 9 && k <= 11))
                            {
                                cell = row.CreateCell(k);
                                cell.CellFormula = "Sum(" + footer2[k - 6] + "7:" + footer2[k - 6] + rowIndex.ToString() + ")";
                                cell.Row.Height = 500;
                                cell.CellStyle = hs4;
                            }

                        }
                        //fix lai border
                        for (int k = 0; k < dt1.Columns.Count - 5; k++)
                        {
                            if (k != 4 && k != 6 && k != 10 && k != 11 && k != 13)
                            {
                                r1c1 = row.CreateCell(k);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;
                            }
                        }
                        for (int ik = 0; ik < dt1.Columns.Count - 5; ik++)
                        {
                            sheet.AutoSizeColumn(ik);
                        }
                        #endregion
                    }
                }
                #endregion

                #region TongCongNo
                //Load tong cong no

                string[] header3 = { "Tên khách hàng", "Công trình", "Mác HĐ", "Khối lượng bán", "Loại đá", "Đơn giá", "Thành tiền bê tông", "Hỗ trợ cước", "Tiền ca bơm" };

                if (dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
                    index = 1;
                else if (dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex > 0 && dlLoaiDa.SelectedIndex.Equals(0))
                    index = 2;
                else if (dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex > 0 && dlLoaiDa.SelectedIndex > 0)
                    index = 3;
                else if (dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex.Equals(0) && dlMacHD.SelectedIndex > 0 && dlLoaiDa.SelectedIndex > 0)
                    index = 4;

                getDate();
                p = new SqlParameter[7];
                p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                p[0].SqlDbType = SqlDbType.SmallDateTime;
                p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                p[1].SqlDbType = SqlDbType.SmallDateTime;
                p[2] = new SqlParameter("@Index", index);
                p[2].SqlDbType = SqlDbType.Int;
                p[3] = new SqlParameter("@CongTrinh", ex.GetGuid(dlCongTrinh.SelectedValue));
                p[3].SqlDbType = SqlDbType.UniqueIdentifier;
                p[4] = new SqlParameter("@MacHD", ex.GetGuid(dlMacHD.SelectedValue));
                p[4].SqlDbType = SqlDbType.UniqueIdentifier;
                p[5] = new SqlParameter("@LoaiDa", int.Parse(dlLoaiDa.SelectedValue));
                p[5].SqlDbType = SqlDbType.Int;
                p[6] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
                p[6].SqlDbType = SqlDbType.UniqueIdentifier;

                dt1 = ex.GetData("sp_BCDT_MacHD_CongTrinh_TongCongNo", p);

                sheet = workbook.CreateSheet("Tổng hợp sản lượng");
                rowIndex = 0;
                //Start header-----------------------------------------------------------------------
                #region Header
                //tao hnag dau tien

                //cong ty
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                r1c1.CellStyle = hs1;
                r1c1.Row.Height = 400;

                cra = new CellRangeAddress(0, 0, 0, 4);
                sheet.AddMergedRegion(cra);

                //chi nhanh
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                r1c1.CellStyle = hs1;
                r1c1.Row.Height = 400;

                cra = new CellRangeAddress(1, 1, 0, 4);
                sheet.AddMergedRegion(cra);

                //tieu de bao cao
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue("BẢNG TỔNG HỢP SẢN LƯỢNG " + ngaythang);
                r1c1.CellStyle = hs2;
                r1c1.Row.Height = 500;

                cra = new CellRangeAddress(2, 2, 0, 9);
                sheet.AddMergedRegion(cra);

                string ct2 = dlCongTrinh.SelectedIndex > 0 ? "-" + dlCongTrinh.SelectedItem.Text.Trim() : "";
                //khachhang
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue("Khách hàng: " + dlKhachHang.SelectedItem.Text + ct2);
                r1c1.CellStyle = hs3;
                r1c1.Row.Height = 500;

                cra = new CellRangeAddress(3, 3, 0, 9);
                sheet.AddMergedRegion(cra);

                //freeze panes
                sheet.CreateFreezePane(0, 6);
                #endregion
                //End header-----------------------------------------------------------------------
                #region Header1-2

                //header2

                rowIndex = rowIndex + 2;
                row = sheet.CreateRow(rowIndex);

                cell = row.CreateCell(0);
                cell.SetCellValue("STT");
                cell.CellStyle = hs4;
                cell.Row.Height = 400;

                //tao tieu de cot
                for (int j = 0; j < header3.Length; j++)
                {
                    cell = row.CreateCell(j + 1);
                    cell.SetCellValue(header3[j].ToString());
                    cell.CellStyle = hs4;
                    cell.Row.Height = 500;
                }
                #endregion

                STT = 0;
                //ghi sheet con------------------------------------------
                sodong = 0;
                for (int i = 0; i < dt1.Rows.Count; i++)
                {
                    STT++;

                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);

                    cell = row.CreateCell(0);
                    cell.SetCellValue((STT).ToString());
                    cell.CellStyle = hsCenter;


                    for (int j = 1; j <= dt1.Columns.Count; j++)
                    {

                        cell = row.CreateCell(j);
                        if (j == 4 || j >= 6)
                        {
                            if (!dt1.Rows[i][j - 1].ToString().Trim().Equals(""))
                            {
                                cell.SetCellType(CellType.NUMERIC);
                                cell.SetCellValue(double.Parse(dt1.Rows[i][j - 1].ToString()));
                            }
                            else
                                cell.SetCellValue(dt1.Rows[i][j - 1].ToString());
                        }
                        else cell.SetCellValue(dt1.Rows[i][j - 1].ToString());

                        if (j == 4 || j >= 6)
                            cell.CellStyle = hsRight;
                        else
                            if (j == 3)
                                cell.CellStyle = hsCenter;
                            else
                                cell.CellStyle = headerLabelCellStyle;

                        cell.Row.Height = 500;

                    }
                    if (i > 0)
                    {
                        if (dt1.Rows[i][0].ToString().Equals(dt1.Rows[i - 1][0].ToString()))
                        {
                            sodong++;
                            cra = new CellRangeAddress(rowIndex - sodong, rowIndex, 9, 9);
                            sheet.AddMergedRegion(cra);
                        }
                        else
                            sodong = 0;
                    }
                    if (i == dt1.Rows.Count - 1)
                    {
                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);

                        cell = row.CreateCell(1);
                        cell.SetCellValue("Tổng cộng");
                        cell.CellStyle = hs4;
                        cell.Row.Height = 500;

                        cell = row.CreateCell(4);
                        cell.CellFormula = "Sum(E7:E" + rowIndex.ToString() + ")";
                        cell.CellStyle = hs4;
                        cell.Row.Height = 500;

                        cell = row.CreateCell(7);
                        cell.CellFormula = "Sum(H7:H" + rowIndex.ToString() + ")";
                        cell.CellStyle = hs4;
                        cell.Row.Height = 500;

                        cell = row.CreateCell(8);
                        cell.CellFormula = "Sum(I7:I" + rowIndex.ToString() + ")";
                        cell.CellStyle = hs4;

                        cell = row.CreateCell(9);
                        cell.CellFormula = "J7";
                        cell.CellStyle = hs4;
                        cell.Row.Height = 500;

                        //fix lai bober
                        cell = row.CreateCell(0);
                        cell.CellStyle = hs4;
                        cell.Row.Height = 500;

                        cell = row.CreateCell(2);
                        cell.CellStyle = hs4;
                        cell.Row.Height = 500;

                        cell = row.CreateCell(3);
                        cell.CellStyle = hs4;
                        cell.Row.Height = 500;

                        cell = row.CreateCell(5);
                        cell.CellStyle = hs4;
                        cell.Row.Height = 500;

                        cell = row.CreateCell(6);
                        cell.CellStyle = hs4;
                        cell.Row.Height = 500;
                    }
                }


                for (int ik = 0; ik <= dt1.Columns.Count; ik++)
                {
                    sheet.AutoSizeColumn(ik);
                }
                #endregion
            }

            using (var exportData = new MemoryStream())
            {
                workbook.Write(exportData);
                string filename = dlKhachHang.SelectedIndex > 0 ? dlKhachHang.SelectedItem.Text : dlLoaiKH.SelectedIndex > 0 ? dlLoaiKH.SelectedItem.Text : "";
                string saveAsFileName = "BCDT-MacHD-" + filename + " " + ngaythang + ".xls";

                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
                Response.Clear();
                Response.BinaryWrite(exportData.GetBuffer());
                Response.End();
            }
        }
    }
    private double ReturnCaBomCongTrinh(string Tungay, string denngay, string congtrinh)
    {
        double tiencabom = 0;
        p = new SqlParameter[3];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(Tungay));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(denngay));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@CongTrinh", ex.GetGuid(congtrinh));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;

        dt = ex.GetData("sp_BCDT_GetTongTienCaBom_CongTrinh", p);
        if (dt.Rows.Count > 0)
        {
            tiencabom = double.Parse(dt.Rows[0]["TienCaBom"].ToString());
        }
        else
        {
            tiencabom = 0;
        }
        return tiencabom;
    }
    private void Search(int page)
    {
        int index = 3;
        if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex.Equals(0) && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
            index = 1;
        else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex.Equals(0) && dlCongTrinh.SelectedIndex.Equals(0) && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
            index = 2;
        else if (dlLoaiKH.SelectedIndex.Equals(0) && dlKhachHang.SelectedIndex.Equals(0) && dlCongTrinh.SelectedIndex.Equals(0) && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
            index = 3;
        else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
            index = 4;
        else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex > 0 && dlLoaiDa.SelectedIndex.Equals(0))
            index = 5;
        else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex > 0 && dlLoaiDa.SelectedIndex > 0)
            index = 6;

        getDate();
        p = new SqlParameter[10];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@LoaiKH", ex.GetGuid(dlLoaiKH.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;
        p[3] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
        p[3].SqlDbType = SqlDbType.UniqueIdentifier;
        p[4] = new SqlParameter("@CongTrinh", ex.GetGuid(dlCongTrinh.SelectedValue));
        p[4].SqlDbType = SqlDbType.UniqueIdentifier;
        p[5] = new SqlParameter("@Index", index);
        p[5].SqlDbType = SqlDbType.Int;
        p[6] = new SqlParameter("@PageNumber", page);
        p[6].SqlDbType = SqlDbType.Int;
        p[7] = new SqlParameter("@RowspPage", PageNumber);
        p[7].SqlDbType = SqlDbType.Int;
        p[8] = new SqlParameter("@MacHD", ex.GetGuid(dlMacHD.SelectedValue));
        p[8].SqlDbType = SqlDbType.UniqueIdentifier;
        p[9] = new SqlParameter("@LoaiDa", int.Parse(dlLoaiDa.SelectedValue));
        p[9].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_BCDT_MacHD", p);
        GV.DataBind();

        p = new SqlParameter[8];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@LoaiKH", ex.GetGuid(dlLoaiKH.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;
        p[3] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
        p[3].SqlDbType = SqlDbType.UniqueIdentifier;
        p[4] = new SqlParameter("@Index", index);
        p[4].SqlDbType = SqlDbType.Int;
        p[5] = new SqlParameter("@CongTrinh", ex.GetGuid(dlCongTrinh.SelectedValue));
        p[5].SqlDbType = SqlDbType.UniqueIdentifier;
        p[6] = new SqlParameter("@LoaiDa", int.Parse(dlLoaiDa.SelectedValue));
        p[6].SqlDbType = SqlDbType.Int;
        p[7] = new SqlParameter("@MacHD", ex.GetGuid(dlMacHD.SelectedValue));
        p[7].SqlDbType = SqlDbType.UniqueIdentifier;

        if (GV.Rows.Count > 0)
        {
            dt = ex.GetData("sp_BCDT_MacHD_GetFooter", p);
            if (dt.Rows.Count > 0)
            {
                kltx = double.Parse(dt.Rows[0]["KLThucXuat"].ToString());
                klban = double.Parse(dt.Rows[0]["KLKhachHang"].ToString());
                ttvl = double.Parse(dt.Rows[0]["TTVL"].ToString());
                ttvlkhongvat = double.Parse(dt.Rows[0]["TTVLKhongVAT"].ToString());
                ttvlth = double.Parse(dt.Rows[0]["TTVLTH"].ToString());
                ttban = double.Parse(dt.Rows[0]["TTGiaHD"].ToString());
                ttbankhongvat = double.Parse(dt.Rows[0]["TTGiaHDKhongVAT"].ToString());
                tongchenh = double.Parse(dt.Rows[0]["ChenhKL"].ToString());
                chenhnvkd = double.Parse(dt.Rows[0]["KLNVKD"].ToString());
                chenhcongty = double.Parse(dt.Rows[0]["KLCongTy"].ToString());
                ttchenh = double.Parse(dt.Rows[0]["TTChenh"].ToString());
                ttcongty = double.Parse(dt.Rows[0]["TTCongTy"].ToString());
                ttnvkd = double.Parse(dt.Rows[0]["TTNVKD"].ToString());
                ttcongtron = double.Parse(dt.Rows[0]["TTCongTron"].ToString());
                ttcvc = double.Parse(dt.Rows[0]["TTCVC"].ToString());
                dthugiavl = double.Parse(dt.Rows[0]["DTGiaVL"].ToString());
                dthuvlth = double.Parse(dt.Rows[0]["DTTHVL"].ToString());
                hotrocuoc = double.Parse(dt.Rows[0]["TienHoTroCuoc"].ToString());

                GV.FooterRow.Cells[7].Text = "Tổng cộng";
                GV.FooterRow.Cells[7].HorizontalAlign = HorizontalAlign.Center;
                GV.FooterRow.Cells[10].Text = ex.ConvertDecimal(kltx);
                GV.FooterRow.Cells[12].Text = ex.ConvertDecimal(klban);

                GV.FooterRow.Cells[17].Text = ex.ConvertDecimal(ttvl);
                GV.FooterRow.Cells[18].Text = ex.ConvertDecimal(ttvlkhongvat);
                GV.FooterRow.Cells[19].Text = ex.ConvertDecimal(ttvlth);

                GV.FooterRow.Cells[22].Text = ex.ConvertDecimal(ttban);
                GV.FooterRow.Cells[23].Text = ex.ConvertDecimal(ttbankhongvat);

                GV.FooterRow.Cells[24].Text = ex.ConvertDecimal(tongchenh);
                GV.FooterRow.Cells[25].Text = ex.ConvertDecimal(chenhnvkd);
                GV.FooterRow.Cells[26].Text = ex.ConvertDecimal(chenhcongty);

                GV.FooterRow.Cells[27].Text = ex.ConvertDecimal(ttchenh);
                GV.FooterRow.Cells[28].Text = ex.ConvertDecimal(ttnvkd);
                GV.FooterRow.Cells[29].Text = ex.ConvertDecimal(ttcongty);


                GV.FooterRow.Cells[31].Text = ex.ConvertDecimal(ttcongtron);
                GV.FooterRow.Cells[33].Text = ex.ConvertDecimal(ttcvc);
                GV.FooterRow.Cells[34].Text = ex.ConvertDecimal(dthugiavl);

                GV.FooterRow.Cells[35].Text = ex.ConvertDecimal(dthuvlth);
                GV.FooterRow.Cells[36].Text = ex.ConvertDecimal(hotrocuoc);


                try
                {
                    p = new SqlParameter[4];
                    p[0] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
                    p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[1] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                    p[1].SqlDbType = SqlDbType.SmallDateTime;
                    p[2] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                    p[2].SqlDbType = SqlDbType.SmallDateTime;
                    p[3] = new SqlParameter("@LoaiKH", ex.GetGuid(dlLoaiKH.SelectedValue));
                    p[3].SqlDbType = SqlDbType.UniqueIdentifier;
                    dt = ex.GetData("sp_BCDT_GetTongTienCaBom", p);
                    if (dt.Rows.Count > 0)
                    {
                        tiencabom = double.Parse(dt.Rows[0]["TienCaBom"].ToString());
                        GV.FooterRow.Cells[38].Text = ex.ConvertDecimal(tiencabom);
                    }
                    else
                    {
                        tiencabom = 0;
                        GV.FooterRow.Cells[38].Text = "0";
                    }
                }
                catch
                {
                    tiencabom = 0;
                }
                GV.FooterRow.Cells[39].Text = ex.ConvertDecimal(ttban + ttbankhongvat + hotrocuoc + tiencabom);
            }
        }
    }
    protected void btnPrint_Click(object sender, EventArgs e)
    {
        if (f_InBaoCao.Equals(1))
        {
            try
            {
                if (f_InBaoCao.Equals(1))
                {
                    int ind = 3;
                    if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex.Equals(0) && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
                        ind = 1;
                    else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex.Equals(0) && dlCongTrinh.SelectedIndex.Equals(0) && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
                        ind = 2;
                    else if (dlLoaiKH.SelectedIndex.Equals(0) && dlKhachHang.SelectedIndex.Equals(0) && dlCongTrinh.SelectedIndex.Equals(0) && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
                        ind = 3;
                    else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
                        ind = 4;
                    else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex > 0 && dlLoaiDa.SelectedIndex.Equals(0))
                        ind = 5;
                    else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex > 0 && dlLoaiDa.SelectedIndex > 0)
                        ind = 6;


                    getDate();
                    p = new SqlParameter[8];
                    p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                    p[0].SqlDbType = SqlDbType.SmallDateTime;
                    p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                    p[1].SqlDbType = SqlDbType.SmallDateTime;
                    p[2] = new SqlParameter("@LoaiKH", ex.GetGuid(dlLoaiKH.SelectedValue));
                    p[2].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[3] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
                    p[3].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[4] = new SqlParameter("@Index", ind);
                    p[4].SqlDbType = SqlDbType.Int;
                    p[5] = new SqlParameter("@CongTrinh", ex.GetGuid(dlCongTrinh.SelectedValue));
                    p[5].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[6] = new SqlParameter("@MacHD", ex.GetGuid(dlMacHD.SelectedValue));
                    p[6].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[7] = new SqlParameter("@LoaiDa", int.Parse(dlLoaiDa.SelectedValue));
                    p[7].SqlDbType = SqlDbType.Int;

                    dtPrint = ex.GetData("sp_BCDT_MacHD_Print", p);

                    PrintNPOI(dtPrint);

                }
                else gstGetMess("Bạn không có quyền in báo cáo này", "");
            }
            catch (Exception ax)
            {
                gstGetMess(ax.Message, "");
            }
        }
        else gstGetMess("Bạn không có quyền in báo cáo này", "");
    }
    private void gstGetMess(string gstMess, string gstLink)
    {
        if (gstLink == "")
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "')", true);
        else
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "');window.location.href='" + gstLink + "'", true);

    }

    protected void dlThang_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            txtTuNgay.Visible = true;
            txtDenNgay.Visible = true;
            dlNam.Visible = false;
        }
        else
        {
            txtTuNgay.Visible = false;
            txtDenNgay.Visible = false;
            dlNam.Visible = true;
        }
        dlLoaiKH.SelectedIndex = 0;
        LoadKhachHang(dlKhachHang);
        LoadCongTrinh(dlCongTrinh);
        LoadMacHopDong(dlMacHD);
        LoadLoaiDa(dlLoaiDa);
        getDate();

    }
    public void getDate()
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
        else
        {
            txtTuNgay.Text = "1/" + dlThang.SelectedValue + "/" + dlNam.SelectedValue;
            txtDenNgay.Text = GetLastDayOfMonth(int.Parse(dlThang.SelectedValue)).ToString("dd/MM/yyyy");
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
    }
    public DateTime GetLastDayOfMonth(int iMonth)
    {
        DateTime dtResult = new DateTime(int.Parse(dlNam.SelectedValue), iMonth, 1);
        dtResult = dtResult.AddMonths(1);
        dtResult = dtResult.AddDays(-(dtResult.Day));
        return dtResult;
    }
    protected void dlLoaiKH_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlLoaiKH.SelectedIndex > 0)
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@LoaiKH", ex.GetGuid(dlLoaiKH.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;

            dlKhachHang.Items.Clear();
            dlKhachHang.DataSource = ex.GetData("sp_BCXH_LoadKhachHangByLoaiKH", p);
            dlKhachHang.DataBind();
            dlKhachHang.Items.Insert(0, new ListItem("--Chọn khách hàng--", ""));
            dlKhachHang.SelectedIndex = 0;
        }
        else
            LoadKhachHang(dlKhachHang);

        LoadCongTrinh(dlCongTrinh);
        LoadMacHopDong(dlMacHD);
        LoadLoaiDa(dlLoaiDa);
    }
    protected void btnFirst_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;

        Search(CurrentPage);

        btnPre.Enabled = false;
        btnNext.Enabled = true;
        btnFirst.Enabled = false;
    }
    protected void btnPre_Click(object sender, EventArgs e)
    {
        if (CurrentPage > 1)
        {
            CurrentPage--;
            btnPre.Enabled = true;
            btnNext.Enabled = true;
            btnFirst.Enabled = true;

            Search(CurrentPage);
        }
        else
        {
            btnFirst.Enabled = false;
            btnPre.Enabled = false;
            btnNext.Enabled = true;
        }
    }
    protected void btnNext_Click(object sender, EventArgs e)
    {
        CurrentPage++;

        Search(CurrentPage);

        if (GV.Rows.Count.Equals(0))
        {
            btnPre.Enabled = true;
            btnNext.Enabled = false;
        }
        else
        {
            btnPre.Enabled = true;
            btnNext.Enabled = true;
        }
        btnFirst.Enabled = true;
    }
    protected void GV_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        //if (e.Row.RowType == System.Web.UI.WebControls.DataControlRowType.DataRow)
        //{
        //    e.Row.Attributes.Add("onmouseover", "this.originalstyle=this.style.backgroundColor;this.style.backgroundColor='#EEFFAA'");
        //    e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor=this.originalstyle;");
        //}
        for (int rowIndex = GV.Rows.Count - 2; rowIndex >= 0; rowIndex--)
        {
            GridViewRow gvRow = GV.Rows[rowIndex];
            GridViewRow gvPreviousRow = GV.Rows[rowIndex + 1];

            if (gvRow.Cells[37].Text.Trim().ToLower().Contains("ca") && gvRow.Cells[1].Text.Equals(gvPreviousRow.Cells[1].Text) && gvRow.Cells[7].Text.Equals(gvPreviousRow.Cells[7].Text) && gvRow.Cells[37].Text.Equals(gvPreviousRow.Cells[37].Text) && gvRow.Cells[38].Text.Equals(gvPreviousRow.Cells[38].Text))
            {

                if (gvPreviousRow.Cells[1].RowSpan < 2)
                {
                    gvRow.Cells[1].RowSpan = 2;
                    gvRow.Cells[7].RowSpan = 2;
                    gvRow.Cells[37].RowSpan = 2;
                    gvRow.Cells[38].RowSpan = 2;
                }
                else
                {
                    gvRow.Cells[1].RowSpan = gvPreviousRow.Cells[1].RowSpan + 1;
                    gvRow.Cells[7].RowSpan = gvPreviousRow.Cells[7].RowSpan + 1;
                    gvRow.Cells[37].RowSpan = gvPreviousRow.Cells[37].RowSpan + 1;
                    gvRow.Cells[38].RowSpan = gvPreviousRow.Cells[38].RowSpan + 1;
                }
                gvPreviousRow.Cells[1].Visible = false;
                gvPreviousRow.Cells[7].Visible = false;
                gvPreviousRow.Cells[37].Visible = false;
                gvPreviousRow.Cells[38].Visible = false;
            }
        }
    }
    protected void dlKhachHang_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlKhachHang.SelectedIndex > 0)
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;

            dlCongTrinh.Items.Clear();
            dlCongTrinh.DataSource = ex.GetData("sp_BCXH_LoadCongTrinhByKhachHang", p);
            dlCongTrinh.DataBind();
            dlCongTrinh.Items.Insert(0, new ListItem("--Chọn công trình--", ""));
            dlCongTrinh.SelectedIndex = 0;
        }
        else
            LoadCongTrinh(dlCongTrinh);
        LoadMacHopDong(dlMacHD);
        LoadLoaiDa(dlLoaiDa);
    }
    protected void dlCongTrinh_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlCongTrinh.SelectedIndex > 0)
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@CongTrinh", ex.GetGuid(dlCongTrinh.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;

            dlMacHD.Items.Clear();
            dlMacHD.DataSource = ex.GetData("sp_BCXH_LoadMacHDByCongTrinh", p);
            dlMacHD.DataBind();
            dlMacHD.Items.Insert(0, new ListItem("--Chọn mác HĐ--", ""));
            dlMacHD.SelectedIndex = 0;
        }
        else
            LoadMacHopDong(dlMacHD);
        LoadLoaiDa(dlLoaiDa);
    }
    void LoadMacHopDong(DropDownList dl)
    {
        dl.Items.Clear();
        dl.Items.Insert(0, new ListItem("--Chọn mác HĐ--", ""));
        dl.SelectedIndex = 0;
    }
    void LoadLoaiDa(DropDownList dl)
    {
        dl.Items.Clear();
        dl.Items.Insert(0, new ListItem("--Chọn loại đá--", "0"));
        dl.SelectedIndex = 0;
    }
    protected void dlMacHD_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlMacHD.SelectedIndex > 0)
        {
            p = new SqlParameter[4];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@CongTrinh", ex.GetGuid(dlCongTrinh.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            p[3] = new SqlParameter("@Mac", ex.GetGuid(dlMacHD.SelectedValue));
            p[3].SqlDbType = SqlDbType.UniqueIdentifier;

            dlLoaiDa.Items.Clear();
            dlLoaiDa.DataSource = ex.GetData("sp_BCXH_LoadLoaiDaByMacCongTrinh", p);
            dlLoaiDa.DataBind();
            dlLoaiDa.Items.Insert(0, new ListItem("--Chọn loại đá--", "0"));
            dlLoaiDa.SelectedIndex = 0;
        }
    }
}